﻿# coding: UTF-8
# Filename : createTable.py
from __future__ import with_statement
import ConfigParser
import MySQLdb
import codecs
import time, datetime
import sys,os
import traceback
import threading
reload(sys) 
sys.setdefaultencoding('utf-8')


#读取配置文件中指定字段的值 
def readFromIni(filePath,key):
    config=ConfigParser.ConfigParser()
    with open(filePath,'r') as cfgfile:
        config.readfp(cfgfile)
        value=config.get('info',key)
        return value 
#获取当前执行文件的绝对路径
linux_file_path = sys.path[0] + os.sep + 'init.ini';
url = readFromIni(linux_file_path,'url').strip()
database = readFromIni(linux_file_path,'database').strip()
username = readFromIni(linux_file_path,'username').strip()
password = readFromIni(linux_file_path,'password').strip()
charset = readFromIni(linux_file_path,'charset').strip()
fromMail = readFromIni(linux_file_path,'fromMail').strip()
mailserver = readFromIni(linux_file_path,'mailserver').strip()
to_emails = readFromIni(linux_file_path,'to_emails').strip()

rule = readFromIni(linux_file_path,'rule').strip()

#    发送邮件  
def sendMail(subject,content):
    os.system('sendemail -xu \"warning\" -xp \"oyesgo2010\" -f \"'+fromMail+'\" -t \"'+to_emails+'\" -s \"'+mailserver+'\" -u \"'+subject+'\" -o "message-charset=UTF-8" -m \"'+content+'\"')    



#获取连接
def createConnection():
    conn = MySQLdb.Connection(url, username, password, database, use_unicode=True,charset='utf8')
    return conn
#计算当前日期是  当年的第多少个星期
def returnWeekNum(date):
    date_split = date.split('-')
    year = int(date_split[0])
    month = int(date_split[1])
    day = int(date_split[2])
    d1=datetime.date(year,month,day)
    dds = d1.isocalendar()
    return dds

'''   获取表名  '''
def getTableName(headTable,date):
    if len(rule)==0:
      date_split = date.split('-')
      year = int(date_split[0])
      month = int(date_split[1])
      table_name=headTable+'%d%s%d' %(year,'_',month)
      return table_name
    elif int(rule)==1:#按旬产生表名   规则： headTableYYYYMM_SN
          ''' 当前月上旬第一天 '''
          date_split = date.split('-')
          year = int(date_split[0])
          month_str = date_split[1]
          month = int(month_str)
          date_int = int(date_split[2])
          
          if  date_int>=1 and date_int <=10:
              table_name = headTable+'%d%s%s' %(year,month_str,'_1')
              return table_name
          elif date_int>10 and date_int <=20:
              table_name = headTable+'%d%s%s' %(year,month_str,'_2')
              return table_name
          elif date_int> 20 <=31:
              table_name = headTable+'%d%s%s' %(year,month_str,'_3')
              return table_name
    elif int(rule)==2:#按月产生表名   规则： headTableYYYY_MM
          date_split = date.split('-')
          year = int(date_split[0])
          month = int(date_split[1])
          table_name=headTable+'%d%s%d' %(year,'_',month)
          return table_name
    elif int(rule)==3:#按周建表       规则： headTableYYYY_dek_Weekth
        dateY = returnWeekNum(date)
        table_name = headTable+'%d%s%d' %(dateY[0],'_dek_',dateY[1])
        return table_name
    else:
        date_split = date.split('-')
        year = int(date_split[0])
        month = int(date_split[1])
        table_name=headTable+'%d%s%d' %(year,'_',month)
        return table_name
  
#创建表            
def returnTable (headTable,date):
   conn = createConnection()
   curs = conn.cursor()
   tableName = getTableName(headTable,date)
   try:
       isexist_talbe_sql = 'select count(`TABLE_NAME`) from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`=\'%s\' and `TABLE_NAME`=\'%s\' ' %(database,tableName)
       curs.execute(isexist_talbe_sql)
       isexist_table = curs.fetchall()
       if isexist_table[0][0]==0:
          print u'%s表不存在，创建数据表' %(tableName)
          createsql='CREATE TABLE %s (' %(tableName)
          createsql=createsql+ '`id` int(11) NOT NULL auto_increment,'
          createsql=createsql+ '`cllick_date` datetime default NULL COMMENT \'日志记录日\','
          createsql=createsql+ '`ip_source` varchar(32) default NULL COMMENT \'用户ip\','
          createsql=createsql+ '`user_agent` varchar(512) default NULL COMMENT \'用户代理类型\','
          createsql=createsql+ '`local_url` varchar(512) default NULL COMMENT \'本地url\','
          createsql=createsql+ '`ref` varchar(64) default NULL COMMENT \'来源\','
          createsql=createsql+ '`outer_url` varchar(512) default NULL COMMENT \'外网地址\','
          createsql=createsql+ '`inner_url` varchar(512) default NULL COMMENT \'内部地址\','
          createsql=createsql+'`userid` varchar(512) default NULL COMMENT \'用户标识\','
          createsql=createsql+'`page_type` varchar(32) default NULL COMMENT \'页面类型\','
          createsql=createsql+ '`page_posi` varchar(32) default NULL COMMENT \'页面位置\','
          createsql=createsql+ '`modaction` varchar(32) default NULL COMMENT \'页面动作类型\','
          createsql=createsql+ '`save_date` varchar(32) default NULL COMMENT \'保存日期\','
          createsql=createsql+  '`leave_code` varchar(32) default NULL COMMENT \'出发城市代码,三字码\','
          createsql=createsql+ '`arive_code` varchar(32) default NULL COMMENT \'目的城市代码,三字码\','
          createsql=createsql+ '`leave_name` varchar(256) default NULL COMMENT \'出发城市名称\','
          createsql=createsql+ '`arive_name` varchar(256) default NULL COMMENT \'目的城市名称\','
          createsql=createsql+ '`leave_date` varchar(32) default NULL COMMENT \'机票出发日，酒店入住日\','
          createsql=createsql+ '`back_date` varchar(32) default NULL COMMENT \'机票返程日,酒店离开日\','
          createsql=createsql+ '`provider` varchar(32) default NULL COMMENT \'供应商名称\','
          createsql=createsql+ '`price` varchar(20) default NULL COMMENT \'机票价格,酒店房间价格\','
          createsql=createsql+ '`flight_code` varchar(32) default NULL COMMENT \'航班号\','
          createsql=createsql+ '`bin` varchar(16) default NULL COMMENT \'仓位\','
          createsql=createsql+ '`out_time` varchar(16) default NULL COMMENT \'起飞时间\','
          createsql=createsql+ '`arr_time` varchar(16) default NULL COMMENT \'降落时间\','
          createsql=createsql+ '`provider_code` varchar(64) default NULL COMMENT \'供应商供三字码\','
          createsql=createsql+' `channel_info` varchar(512) default NULL COMMENT \'酒店信息,签证类型,酒店所属城市\','
          createsql=createsql+' `visas_load_url` varchar(512) default NULL COMMENT \'证签跳转到的供应商地址\','
          createsql=createsql+'  `hotel_addr` varchar(512) default NULL COMMENT \'酒店地址\','
          createsql=createsql+'  `hotel_room_type` varchar(64) default NULL COMMENT \'酒店房间类型\','
          createsql=createsql+'  `hotel_type` varchar(128) default NULL COMMENT \'酒店星级\','
          createsql=createsql+'  `allowance_1` varchar(128) default NULL COMMENT \'预留字段1\','
          createsql=createsql+'  `allowance_2` varchar(64) default NULL COMMENT \'预留字段2\','
          createsql=createsql+'  `allowance_3` varchar(64) default NULL COMMENT \'预留字段3\','
          createsql=createsql+'  `allowance_4` varchar(64) default NULL COMMENT \'预留字段4\','
          createsql=createsql+'  `allowance_5` varchar(64) default NULL COMMENT \'预留字段5\','
          createsql=createsql+ 'PRIMARY KEY  (`id`)'
          createsql=createsql+') ENGINE=InnoDB DEFAULT CHARSET=utf8;'
          curs.execute(createsql)
          return tableName
       else:
           print u'%s表已存在' %(tableName)
           return tableName
   except Exception,e:
       print e.message
       print traceback.format_exc()
       print datetime.datetime.now()
       sendMail('%s号获取%s表明错误,或创建数据表错误' %(date,tableName), e.message)
   finally:
       conn.commit()
       curs.close()
       conn.close() 
